package com.felight.ems.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.log4j.Logger;
import com.felight.examples.jdbc.db.DbUtil;

public class UserDao {
	
		private Logger log = Logger.getLogger(UserDao.class);
		private Connection connection;
		private Statement statement;
		private PreparedStatement preparedStatement;
	
		public User getUser(int userId){
			String query = "SELECT * FROM USER WHERE USER_ID = "+ userId;
			ResultSet rs = null;
			User user = null;
			try {
				connection = ConnectionFactory.getConnection();
				statement = connection.createStatement();
				rs = statement.executeQuery(query);
				if(rs.next()){
					user = new User();
					user.setUserId(rs.getInt("USER_ID"));
					user.setUserName(rs.getString("USER_NAME"));
					user.setEmail(rs.getString("EMAIL"));
					user.setFirstName(rs.getString("FIRST_NAME"));
					user.setLastName(rs.getString("LAST_NAME"));
					user.setPassword(rs.getString("PASSWORD"));
					user.setUserType(rs.getString("USER_TYPE"));
					user.setActiveFlag(rs.getString("ACTIVE_FLAG"));
					user.setCreatedBy(rs.getInt("CREATED_BY"));
					user.setCreatedDate(rs.getDate("CREATED_DATE"));
					user.setModifiedBy(rs.getInt("MODIFIED_BY"));
					user.setModifiedDate(rs.getDate("MODIFIED_DATE"));
					user.setDob(rs.getDate("DATE_OF_BIRTH"));
					user.setCoins(rs.getInt("COINS"));
					user.setNoOfBusiness(rs.getInt("NO_OF_BUSINESS"));
					log.info(query + "exectued successfully");
					return user;
				}
			}
			catch(SQLException e){
				log.debug("Select query unsuccessful : QUERY : "+query);
				log.debug("due to :"+e);
			}
			finally{
				DbUtil.close(rs);
				DbUtil.close(statement);
				DbUtil.close(connection);
			}
			return user;
		}
		public boolean saveUser(User user){
			String query = "INSERT INTO USER (USER_NAME, EMAIL, FIRST_NAME, LAST_NAME, PASSWORD, USER_TYPE, ACTIVE_FLAG, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, DATE_OF_BIRTH"
						+ "COINS, NO_OF_BUSINESS) VALUES"
						+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			try{
				connection = ConnectionFactory.getConnection();
				preparedStatement = connection.prepareStatement(query);
				preparedStatement.setString(1, user.getUserName());
				preparedStatement.setString(2, user.getEmail());
				preparedStatement.setString(3, user.getFirstName());
				preparedStatement.setString(4, user.getLastName());
				preparedStatement.setString(5, user.getPassword());
				preparedStatement.setString(6, user.getUserType());
				preparedStatement.setString(7, user.getActiveFlag());
				preparedStatement.setInt(8, user.getCreatedBy());
				preparedStatement.setDate(9, user.getCreatedDate());
				preparedStatement.setInt(10, user.getModifiedBy());
				preparedStatement.setDate(11, user.getModifiedDate());
				preparedStatement.setDate(12, user.getDob());
				preparedStatement.setInt(13, user.getCoins());
				preparedStatement.setInt(14, user.getNoOfBusiness());
				log.info("NEw row added to database with query : "+ query);
				return preparedStatement.executeUpdate() > 0 ? true : false;
			}
			catch(SQLException ex){
				log.error("Insertion failed due to : "+ ex);
				return false;
			}
			finally{
				DbUtil.close(preparedStatement);
				DbUtil.close(connection);
			}
		}
		public boolean updateUser(User user){
			String query = "UPDATE USER SET USER_NAME = ?, EMAIL=?, FIRST_NAME=?, LAST_NAME=?,"
						+ "PASSWORD=?, USER_TYPE=?, ACTIVE_FLAG=?, CREATED_BY=?,"
						+"CREATED_DATE=?, MODIFIED_BY=?,MODIFIED_DATE=?,DATE_OF_BIRTH=?,"
						+"COINS=?,NO_OF_BUSINESS=? WHERE USER_ID="+user.getUserId();
			try{
				connection = ConnectionFactory.getConnection();
				preparedStatement = connection.prepareStatement(query);
				preparedStatement.setString(1, user.getUserName());
				preparedStatement.setString(2, user.getEmail());
				preparedStatement.setString(3, user.getFirstName());
				preparedStatement.setString(4, user.getLastName());
				preparedStatement.setString(5, user.getPassword());
				preparedStatement.setString(6, user.getUserType());
				preparedStatement.setString(7, user.getActiveFlag());
				preparedStatement.setInt(8, user.getCreatedBy());
				preparedStatement.setDate(9, user.getCreatedDate());
				preparedStatement.setInt(10, user.getModifiedBy());
				preparedStatement.setDate(11, user.getModifiedDate());
				preparedStatement.setDate(12, user.getDob());
				preparedStatement.setInt(13, user.getCoins());
				preparedStatement.setInt(14, user.getNoOfBusiness());
				log.info("Row updated successful with query :"+query);
				return preparedStatement.executeUpdate() >0 ? true : false;
			}
			catch(SQLException ex){
				log.error("Update failed due to :"+ ex);
				return false;
			}
			finally{
				DbUtil.close(preparedStatement);
				DbUtil.close(connection);
			}
		}
		public boolean deleteUser(User user){
			String query = "DELETE from USER WHERE USER_ID ="+user.getUserId();
			try{
				connection = ConnectionFactory.getConnection();
				preparedStatement = connection.prepareStatement(query);
				log.info("Row deleted successfully with query :"+ query);
				return preparedStatement.executeUpdate() > 0 ? true : false;
			}
			catch(SQLException ex){
				log.error("Delete failed due to : " + ex);
				return false;
			}
			finally {
				DbUtil.close(preparedStatement);
				DbUtil.close(connection);
			}
		}
}
